Imports RIBS.Code.DBCode
Imports RIBS.Code.GlobalModule1

Public Class Reports

    Public Function Closing() As DataSet
        Try
            Dim ds As New DataSet

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ContactMethodHeader(ByVal ds As DataSet) As DataSet
        Try
            Dim oRibs As New RibsClass
            Dim iCol As Int16 = 1
            Dim ldt As DataTable = oRibs.GetContactMethods()
            Dim dr As DataRow

            dr = ds.Tables("ContactMethodHeader").NewRow
            For Each xdr As DataRow In ldt.Rows
                dr(iCol) = xdr(0)
                iCol += 1
            Next
            ds.Tables("ContactMethodHeader").Rows.Add(dr)
            Return ds

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function UserDataContactMethod() As DataTable
        Try
            Dim oRibs As New RibsClass
            Dim ldt As New DataTable("UserData")
            Dim dt As New DataTable
            Dim iRow As Int16 = 1
            dt = oRibs.GetContactMethods(True)

            ldt.Columns.Add("UserID", GetType(String))
            ldt.Columns.Add("UserID", GetType(String))

            ldt.Columns.Add("Dollars", GetType(String))
            ldt.Columns.Add("WriteOff", GetType(Double))
            ldt.Columns.Add("Retained", GetType(Double))
            ldt.Columns.Add("Policy_Number", GetType(String))
            ldt.Columns.Add("Total Calls Made", GetType(String))
            For Each xdr As DataRow In dt.Rows
                Dim dc As New DataColumn(xdr(0), GetType(Int32))
                ldt.Columns.Add(dc)
            Next

            Return ldt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function GetUserWorkByContactMethod(ByVal UserID As String, _
                                                ByVal tblActions As DataTable, _
                                                ByVal FromDate As Date, _
                                                ByVal ToDate As Date) As DataTable
        Dim dt As DataTable
        Try
            Dim aAction As String = " ("
            For Each dr As DataRow In tblActions.Rows
                aAction += "'" & dr("Action") & "',"
            Next
            aAction = " AND ActionHistory.Action IN " & Left(aAction, Len(aAction) - 1) & ")"

            dt = ExecuteSQL("SELECT DISTINCT Ribs.POLICY_NUMBER, Letter_Code, Ribs.Account_SYSTEM, ActionHistory.Action, ActionHistory.ContactMethod, " & _
                            " Count(ActionHistory.ContactMethod) AS CountOfContactMethod, Ribs.AMOUNT_DUE, Ribs.NEW_PREMIUM_AMOUNT, " & _
                            " Ribs.Retained, Ribs.WriteOff, ActionHistory.RibsID, ActionHistory.User " & _
                            " FROM Ribs INNER JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
                            " WHERE (((ActionHistory.User)='" & UserID & "') " & _
                            " AND ((ActionHistory.EntryDate)>= " & dbDateFormat(FromDate) & _
                            " And (ActionHistory.EntryDate)<= " & dbDateFormat(ToDate) & ")) " & aAction & _
                            " GROUP BY ActionHistory.User,  Ribs.POLICY_NUMBER, Ribs.Letter_Code, Ribs.Account_SYSTEM, ActionHistory.Action, ActionHistory.ContactMethod, " & _
                            " Ribs.AMOUNT_DUE, Ribs.NEW_PREMIUM_AMOUNT, Ribs.Retained, " & _
                            " RIBS.WriteOff, ActionHistory.RibsID" & _
                            " ORDER BY Ribs.POLICY_NUMBER, ActionHistory.ContactMethod; ", "UserWork").Tables("UserWork")

        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function
End Class
